package com.feeyo.monitor.service.imp;

import com.feeyo.monitor.domain.Vehicle;
import com.feeyo.monitor.domain.VehicleType;
import com.feeyo.monitor.enumeration.ProtocolType;
import com.feeyo.monitor.service.DepartmentService;
import com.feeyo.monitor.service.ExcelParserService;
import com.feeyo.monitor.service.VehicleService;
import com.feeyo.monitor.service.VehicleTypeService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author <a href="mailto:bliulei@variflight.com">liulei</a>
 * @Description Created by liulei on 2017/5/16 下午2:24.
 */
@Component
public class VehicleExcelParserService<T> implements ExcelParserService{
    Logger logger = LoggerFactory.getLogger(VehicleExcelParserService.class);
    DecimalFormat dcf = new DecimalFormat("#.0");

    @Autowired
    VehicleService vehicleService;
    @Autowired
    VehicleTypeService vehicleTypeService;
    @Autowired
    DepartmentService departmentService;

    @Override
    public List<T> parse(InputStream stream) throws IOException{
        //Excel文件
        XSSFWorkbook wb = new XSSFWorkbook(stream);
        //Excel工作表
        XSSFSheet sheet = wb.getSheetAt(0);
        //表头那一行
        XSSFRow titleRow = sheet.getRow(0);
        //表头那个单元格
        XSSFCell titleCell = titleRow.getCell(0);
        String title = titleCell.getStringCellValue();
        logger.info("标题是："+title);
        List result = new ArrayList();
        int ddddd = sheet.getLastRowNum();
        for(int i=1; i<sheet.getLastRowNum()+1; i++) {
            XSSFRow row = sheet.getRow(i);
            if(row.getCell(3) == null)
                continue;
            String number = row.getCell(3).getStringCellValue();
            String devid = row.getCell(6).getStringCellValue();
            XSSFCell cellModel = row.getCell(5);
            String model="";
            if (cellModel != null)
                model = row.getCell(5).getStringCellValue();
            String sim = "";
            String dept1 = row.getCell(1).getStringCellValue();
            String dept2 = row.getCell(2).getStringCellValue();
            String airport = row.getCell(8).getStringCellValue();
            String vehType = row.getCell(4).getStringCellValue();
            String vtid = vehicleTypeService.selectIdByName(vehType);
            String protocol = row.getCell(7).getStringCellValue();
            short protocol1 = 0;
            String setup  = row.getCell(9).getStringCellValue();

            if(!protocol.equals("")){
                if(protocol.equals("接入"))
                    protocol1 = 0;
                else if (protocol.equals("JT808"))
                    protocol1 = 1;
                else if (protocol.equals("1808C"))
                    protocol1 = 2;
                else if (protocol.equals("NMEA"))
                    protocol1 = 3;
            }
            if(!setup.equals("")){
                if(setup.equals("免费"))
                    setup="1";
                else if (setup.equals("收费"))
                    setup="2";
                else if (setup.equals("接入"))
                    setup="3";
            }
            String deptId = "";
            if(dept2==null||dept2.equals("")){
                deptId = departmentService.selectIdByDept1(dept1);
            }else{
                String parentId = departmentService.selectIdByDept1(dept1);
                deptId = departmentService.selectIdByDept1AndDept2(dept2,parentId);
            }

            logger.info(number + ":" + devid + ":" + sim + ":" + dept1);

            Vehicle v = new Vehicle();
            v.setNumber(number);
            v.setDevid(devid);
            v.setSim(sim);
            v.setVehicleType(vtid);
            v.setVehiclemodel(model);
            v.setVehiclestatus((short)1);
            v.setDepartment(deptId);
            v.setProtocol(protocol1);
            v.setSetup(setup);
            v.setPosowner(airport);
            v.setInstallTime(new Date());
            v.setComment("");

            vehicleService.insert(v);
        }
        return null;
    }

    @Override
    public String exportRunnings(List list, String path) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("车辆上线时间");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("车牌号");
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue("车辆类型");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("最大速度(km/h)");
        cell.setCellStyle(style);

        cell = row.createCell((short) 3);
        cell.setCellValue("平均速度(km/h)");
        cell.setCellStyle(style);

        cell = row.createCell((short) 4);
        cell.setCellValue("运行里程(km)");
        cell.setCellStyle(style);

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

        for (int i = 0; i < list.size(); i++)
        {
            row = sheet.createRow((int) i + 1);
            Map vehile =  (Map) list.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short)0).setCellValue((String)vehile.get("number"));
            row.createCell((short)1).setCellValue((String)vehile.get("typename"));
            row.createCell((short)2).setCellValue(dcf.format((Double)vehile.get("maxspeed")));
            row.createCell((short)3).setCellValue(dcf.format((Double)vehile.get("avgspeed")));
            row.createCell((short)4).setCellValue(dcf.format((Double)vehile.get("distance")/1000));

        }
        // 第六步，将文件存到指定位置
        try
        {
            String fileName = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + "-运行统计.xls";
            FileOutputStream fout = new FileOutputStream(path + fileName);
            wb.write(fout);
            fout.close();
            return fileName;
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return "";
    }

    @Override
    public String exportAlarms(List list, String path) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("车辆上线时间");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("车牌号");
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue("车辆类型");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("速度(km/h)");
        cell.setCellStyle(style);

        cell = row.createCell((short) 3);
        cell.setCellValue("限制区");
        cell.setCellStyle(style);

        cell = row.createCell((short) 4);
        cell.setCellValue("开始告警");
        cell.setCellStyle(style);

        cell = row.createCell((short) 5);
        cell.setCellValue("结束告警");
        cell.setCellStyle(style);

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

        for (int i = 0; i < list.size(); i++)
        {
            row = sheet.createRow((int) i + 1);
            Map vehile =  (Map) list.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short)0).setCellValue((String)vehile.get("number"));
            row.createCell((short)1).setCellValue((String)vehile.get("vehicletype"));
            row.createCell((short)2).setCellValue(dcf.format((Double)vehile.get("maxspeed")));
            row.createCell((short)3).setCellValue((String)vehile.get("area"));
            row.createCell((short)4).setCellValue((String)vehile.get("intime"));
            row.createCell((short)5).setCellValue((String)vehile.get("outtime"));
        }
        // 第六步，将文件存到指定位置
        try
        {
            String fileName = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + "-告警记录.xls";
            FileOutputStream fout = new FileOutputStream(path + fileName);
            wb.write(fout);
            fout.close();
            return fileName;
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return "";
    }

    @Override
    public String createVehicleExcel(List list,String path) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("车辆上线时间");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("序号");
        cell.setCellStyle(style);

        cell = row.createCell((short) 1);
        cell.setCellValue("设备标识");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("车牌号");
        cell.setCellStyle(style);

        cell = row.createCell((short) 3);
        cell.setCellValue("车辆型号");
        cell.setCellStyle(style);

        cell = row.createCell((short) 4);
        cell.setCellValue("车辆类型");
        cell.setCellStyle(style);

        cell = row.createCell((short) 5);
        cell.setCellValue("所属部门");
        cell.setCellStyle(style);

        cell = row.createCell((short) 6);
        cell.setCellValue("安装类型");
        cell.setCellStyle(style);
        cell = row.createCell((short) 7);
        cell.setCellValue("SIM卡");
        cell.setCellStyle(style);
        cell = row.createCell((short) 8);
        cell.setCellValue("所属机场");
        cell.setCellStyle(style);
        cell = row.createCell((short) 9);
        cell.setCellValue("车辆状态");
        cell.setCellStyle(style);
        cell = row.createCell((short) 10);
        cell.setCellValue("上线时间");
        cell.setCellStyle(style);

        cell = row.createCell((short) 11);
        cell.setCellValue("安装时间");
        cell.setCellStyle(style);

        cell = row.createCell((short) 12);
        cell.setCellValue("备注");
        cell.setCellStyle(style);

        cell = row.createCell((short) 13);
        cell.setCellValue("付费单位");
        cell.setCellStyle(style);

        cell = row.createCell((short) 14);
        cell.setCellValue("协议类型");
        cell.setCellStyle(style);

        cell = row.createCell((short) 15);
        cell.setCellValue("年检时间");
        cell.setCellStyle(style);

//        cell = row.createCell((short) 12);
//        cell.setCellValue("id");
//        cell.setCellStyle(style);

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

        for (int i = 0; i < list.size(); i++)
        {
            row = sheet.createRow((int) i + 1);
            Map vehile =  (Map) list.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short)0).setCellValue((i+1));
            row.createCell((short)1).setCellValue((String)vehile.get("devid"));
            row.createCell((short)2).setCellValue((String)vehile.get("number"));
            row.createCell((short)3).setCellValue((String)vehile.get("vehiclemodel"));
            row.createCell((short)4).setCellValue((String)vehile.get("typename"));
            row.createCell((short)5).setCellValue((String)vehile.get("deptname"));

            String setup= "";
            if(vehile.get("setup").equals("1"))
                setup="免费";
            else if (vehile.get("setup").equals("2"))
                setup="接入";
            else if (vehile.get("setup").equals("3"))
                setup="收费";
            row.createCell((short)6).setCellValue(setup);//1为自装 2为接入 3免费

            row.createCell((short)7).setCellValue((String)vehile.get("sim"));
            row.createCell((short)8).setCellValue((String)vehile.get("posowner"));
            int vehicleStatus = (int)vehile.get("vehiclestatus");
            switch (vehicleStatus){//1.空闲2.维修3.工作4.未知
                case 1:
                    row.createCell((short)9).setCellValue("空闲");
                    break;
                case 2:
                    row.createCell((short)9).setCellValue("维修");
                    break;
                case 3:
                    row.createCell((short)9).setCellValue("工作");
                    break;
                default:
                    row.createCell((short)9).setCellValue("未知");
            }
            Long lastTime = (Long)vehile.get("lastTime");
            if (lastTime == 1)
                row.createCell((short)10).setCellValue("");
            else {
                String time =  df.format(new Date( lastTime * 1000));
                row.createCell((short)10).setCellValue(time);
            }
            Object installTime = vehile.get("install_time");
            if (installTime != null)
                row.createCell((short)11).setCellValue(installTime.toString());
            row.createCell((short)12).setCellValue((String)vehile.get("comment"));
            row.createCell((short)13).setCellValue((String)vehile.get("charge_unit"));

            int protocol = (int)vehile.get("protocol");
            switch (protocol){//1.空闲2.维修3.工作4.未知
                case 0:
                    row.createCell((short)14).setCellValue("接入");
                    break;
                case 1:
                    row.createCell((short)14).setCellValue("808协议");
                    break;
                case 2:
                    row.createCell((short)14).setCellValue("三金协议");
                    break;
                case 3:
                    row.createCell((short)14).setCellValue("NMEA协议");
                    break;
                default:
                    row.createCell((short)14).setCellValue("未知");
            }

            Object checkTime = vehile.get("check_time");
            if (checkTime != null)
                row.createCell((short)15).setCellValue(checkTime.toString());
        }

        // 第六步，将文件存到指定位置
        try
        {
            String fileName = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + "-上线时间.xls";
            FileOutputStream fout = new FileOutputStream(path + fileName);
            wb.write(fout);
            fout.close();
            return fileName;
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return "";
    }


}
